<?php
/**
 * Created by PHPStorm
 * User: xiest
 * Date: 2021/7/8
 * Email: <939369942@qq.com>
 */
namespace basic\services;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Writer\Exception;
use think\exception\ValidateException;

class OfficeService
{
    public function excelOutput(String $name,String $title,array $field,array $data,string $format = 'Xlsx')
    {
        $objExcel = new Spreadsheet();
            $objExcel->createSheet(0)
            ->setTitle($title);
            $sheet = $objExcel->getActiveSheet();
            $row = 1;
            $column = ord("A");

            foreach ($field as $k=>$v){
                $sheet->setCellValue(chr($column)."$row",$k);
                $column++;
            }
            $sheet->getStyle("A{$row}:".chr($column-1).$row)->getFont()->setBold(true);
            $sheet->getStyle("A{$row}:".chr($column-1).$row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getDefaultColumnDimension()->setWidth(25);
            $row++;

            foreach ($data as $item){

               $column = ord('A');
               foreach ($field as $k=>$v)
               {
                    $sheet->setCellValue(chr($column).$row,$item[$v]??'');
                    $column++;
               }
               $sheet->getStyle("A{$row}:".chr($column-1)."{$row}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
               $sheet->getStyle("A{$row}:".chr($column-1)."{$row}")->getAlignment()->setVertical(Alignment::HORIZONTAL_CENTER);
               $sheet->getStyle("A{$row}:".chr($column-1)."{$row}")->getAlignment()->setWrapText(TRUE);
               $row++;
            }

            if ($format == 'Xlsx') {
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            } elseif ($format == 'Xls') {
                header('Content-Type: application/vnd.ms-excel');
            }

            header("Content-Disposition: attachment;filename=". $name . '.' . strtolower($format));

            header('Cache-Control: max-age=0');

        try {
            $objWriter = IOFactory::createWriter($objExcel, $format);
        } catch (Exception $e) {
            throw new ValidateException('导出失败'.$e->getMessage());
        }
        $objWriter->save('php://output');
    }
}